package jblip.gui.data;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.HashSet;
import java.util.Set;

import jblip.gui.JBlip;
import jblip.resources.Media;
import jblip.resources.Subscription;
import jblip.resources.Transport;
import jblip.resources.Update;
import jblip.resources.User;
import jblip.resources.UserPicture;
import jblip.resources.User.Sex;
import jblip.resources.UserPicture.PictureSize;

import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.jdbcx.JdbcDataSource;
import org.h2.tools.ChangeFileEncryption;
import org.h2.tools.RunScript;

public class DatabaseClient {
  private static String SQL_ME_MERGE =
      "MERGE INTO Media (id, url) VALUES (?, ?)";

  private static String SQL_US_MERGE =
      "MERGE INTO Users (id, name, location, sex, bg_id, bg_path) VALUES (?, ?, ?, ?, ?, ?)";

  private static String SQL_US_GETBYID = "SELECT * FROM Users WHERE id = ?";

  private static String SQL_US_GETBYNAME = "SELECT * FROM Users WHERE name = ?";

  private static String SQL_AV_MERGE =
      "MERGE INTO Avatars (user, valid_thru, path_femto, path_nano, path_pico, path_standard, path_default, path_large) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

  private static String SQL_AV_GET =
      "SELECT * FROM Avatars AS Av WHERE Av.user = ? AND Av.valid_thru > ?";

  private static String SQL_SU_ADD =
      "INSERT INTO Subscriptions (tracking, tracked, transport) VALUES (?, ?, ?)";

  private static String SQL_SU_GETTRACKED =
      "SELECT tracked, transport FROM Subscriptions AS Su WHERE Su.tracking = ?";

  private static String SQL_SU_GETTRACKING =
      "SELECT tracking, transport FROM Subscriptions AS Su WHERE Su.tracked = ?";

  private static String SQL_SU_DELTRACKED =
      "DELETE FROM Subscriptions AS Su WHERE Su.tracking = ?";

  private static String SQL_SU_DELTRACKING =
      "DELETE FROM Subscriptions AS Su WHERE Su.tracked = ?";

  private static long AV_VALID_MILLIS = 3600L * 1000L * 24L; // 24h

  public static DatabaseClient init(final String user, final String passwd) {
    try {
      Class.forName("org.h2.Driver");
    } catch (Exception e) {
      return null;
    }

    final File home = JBlip.instance().getHomeDirectory();
    final File db_file = new File(home.getPath() + File.separator + user);

    try {
      return new DatabaseClient(passwd, db_file);
    } catch (SQLException sqle) {
      sqle.printStackTrace();
      return null;
    }
  }

  public static void reinit(String user, String old_pass, String new_pass) {
    final File home = JBlip.instance().getHomeDirectory();
    try {
      ChangeFileEncryption.execute(home.getPath(), user, "AES", old_pass
          .toCharArray(), new_pass.toCharArray(), true);
    } catch (SQLException e) {
      System.err.println("Couldn't change encryption for " + user);
    }
  }

  private final JdbcConnectionPool pool;

  private DatabaseClient(final String passwd, final File db_file)
      throws SQLException {
    final boolean exists = db_file.exists();
    final JdbcDataSource source = new JdbcDataSource();
    source.setURL(String.format("jdbc:h2:%s;CIPHER=AES", db_file.getPath()));
    source.setUser("blip");
    source.setPassword(String.format("%s %s", passwd, passwd));

    this.pool = JdbcConnectionPool.create(source);

    if (!exists) {
      createTables();
    }
  }

  private void createTables() throws SQLException {
    final InputStream input =
        getClass().getResourceAsStream("/jblip/gui/resources/create.sql");
    RunScript.execute(pool.getConnection(), new InputStreamReader(input));
    try {
      input.close();
    } catch (IOException ioe) {
      // Do nothing
    }
  }

  public synchronized UserPicture getAvatar(final String user) {
    try {
      final Connection conn = pool.getConnection();
      final PreparedStatement stat = conn.prepareStatement(SQL_AV_GET);
      stat.setString(1, user);
      stat.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
      ResultSet result = stat.executeQuery();
      if (!result.next()) {
        conn.close();
        return null;
      }

      final String av_femto = result.getString("path_femto");
      final String av_nano = result.getString("path_nano");
      final String av_pico = result.getString("path_pico");
      final String av_standard = result.getString("path_standard");
      final String av_default = result.getString("path_default");
      final String av_large = result.getString("path_large");

      final UserPicture up = new UserPicture() {
        private static final long serialVersionUID = 1L;

        @Override
        public String getURL(PictureSize size) {
          switch (size) {
            case FEMTO:
              return av_femto;
            case PICO:
              return av_pico;
            case NANO:
              return av_nano;
            case STANDARD:
              return av_standard;
            case DEFAULT:
              return av_default;
            case LARGE:
              return av_large;
          }
          return null;
        }

        @Override
        public String getURL() {
          return getURL(PictureSize.DEFAULT);
        }

        @Override
        public Integer getID() {
          // TODO Auto-generated method stub
          return 0;
        }
      };

      conn.close();
      return up;
    } catch (SQLException sqle) {
      sqle.printStackTrace();
      return null;
    }
  }

  public synchronized String getAvatarPath(final String user,
      final PictureSize size) {
    try {
      final Connection conn = pool.getConnection();
      final String column = "path_" + size.toString().toLowerCase();
      PreparedStatement stat = conn.prepareStatement(SQL_AV_GET);
      stat.setString(1, user);
      stat.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
      if (!stat.execute()) {
        conn.close();
        return null;
      }
      ResultSet set = stat.getResultSet();
      final String result = set.getString(column);
      conn.close();
      return result;
    } catch (SQLException sqle) {
      sqle.printStackTrace();
      return null;
    }
  }

  public synchronized void setAvatar(final String user, final UserPicture avatar) {
    try {
      final Connection conn = pool.getConnection();
      final PreparedStatement stat = conn.prepareStatement(SQL_AV_MERGE);

      stat.setString(1, user);
      stat.setTimestamp(2, new Timestamp(System.currentTimeMillis()
          + AV_VALID_MILLIS));
      stat.setString(3, avatar.getURL(PictureSize.FEMTO));
      stat.setString(4, avatar.getURL(PictureSize.NANO));
      stat.setString(5, avatar.getURL(PictureSize.PICO));
      stat.setString(6, avatar.getURL(PictureSize.STANDARD));
      stat.setString(7, avatar.getURL(PictureSize.DEFAULT));
      stat.setString(8, avatar.getURL(PictureSize.LARGE));
      stat.execute();
      conn.close();
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    }
  }

  public synchronized void setMedia(final Media media) {
    try {
      final Connection conn = pool.getConnection();
      final PreparedStatement stat =
          pool.getConnection().prepareStatement(SQL_ME_MERGE);
      stat.setInt(1, media.getID());
      stat.setString(2, media.getURL());
      stat.execute();
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public synchronized void setUser(final User user) {
    try {
      final Connection conn = pool.getConnection();
      final PreparedStatement stat = conn.prepareStatement(SQL_US_MERGE);
      stat.setInt(1, user.getID());
      stat.setString(2, user.getLogin());
      if (user.getLocation() == null) {
        stat.setNull(3, Types.VARCHAR);
      } else {
        stat.setString(3, user.getLocation());
      }
      stat.setString(4, user.getSex().toString());
      final Media bg = user.getBackground();
      if (bg == null) {
        stat.setNull(5, Types.INTEGER);
      } else {
        setMedia(bg);
        stat.setInt(5, bg.getID());
      }
      final String bg_path = user.getBackgroundPath();
      if (bg_path == null) {
        stat.setNull(6, Types.VARCHAR);
      } else {
        stat.setString(6, bg_path);
      }
      stat.execute();
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  private synchronized void setSubscriptions(final String user,
      final String sql_delete, final Set<Subscription> subs) {
    try {
      final Connection conn = pool.getConnection();
      conn.setAutoCommit(false);
      final PreparedStatement del = conn.prepareStatement(sql_delete);
      del.setString(1, user);
      del.execute();
      final PreparedStatement ins = conn.prepareStatement(SQL_SU_ADD);
      for (Subscription sub : subs) {
        ins.setString(1, sub.getTrackingUserName());
        ins.setString(2, sub.getTrackedUserName());
        ins.setByte(3, sub.getTransport().getID().byteValue());
        ins.execute();
        ins.clearParameters();
      }
      conn.commit();
      conn.setAutoCommit(true);
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public synchronized void setSubscriptionsFrom(final String user,
      final Set<Subscription> subs) {
    setSubscriptions(user, SQL_SU_DELTRACKED, subs);
  }

  public synchronized void setSubscriptionsTo(final String user,
      final Set<Subscription> subs) {
    setSubscriptions(user, SQL_SU_DELTRACKING, subs);
  }

  private synchronized Set<Subscription> getSubscriptions(final String user,
      final String sql_select) {
    final Set<Subscription> subs = new HashSet<Subscription>();
    try {
      final Connection conn = pool.getConnection();
      PreparedStatement stat = conn.prepareStatement(sql_select);
      stat.setString(1, user);
      if (stat.execute()) {
        do {
          final ResultSet result = stat.getResultSet();
          subs.add(new Subscription() {
            private static final long serialVersionUID = 1L;

            @Override
            public Transport getTransport() {
              try {
                return Transport.getStandardTransport(result.getInt(3));
              } catch (SQLException e) {
                e.printStackTrace();
              }
              return Transport.UNKNOWN;
            }

            @Override
            public String getTrackingUserName() {
              try {
                return result.getString(1);
              } catch (SQLException e) {
                e.printStackTrace();
              }
              return null;
            }

            @Override
            public User getTrackingUser() {
              return null;
            }

            @Override
            public String getTrackedUserName() {
              try {
                return result.getString(2);
              } catch (SQLException e) {
                e.printStackTrace();
              }
              return null;
            }

            @Override
            public User getTrackedUser() {
              return null;
            }
          });
        } while (stat.getMoreResults());
      }
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return subs;
  }

  public synchronized Set<Subscription> getSubscriptionsFrom(final String user) {
    return getSubscriptions(user, SQL_SU_GETTRACKED);
  }

  public synchronized Set<Subscription> getSubscriptionsTo(final String user) {
    return getSubscriptions(user, SQL_SU_GETTRACKING);
  }

  private User getUser(PreparedStatement sql_stat) throws SQLException {
    if (sql_stat.execute()) {
      final ResultSet result = sql_stat.getResultSet();
      final Sex sex = Sex.fromString(result.getString("sex"));
      final String login = result.getString("name");
      final String location = result.getString("location");
      final String bg_path = result.getString("bg_path");
      final int id = result.getInt("id");
      return new User() {
        private static final long serialVersionUID = 1L;

        @Override
        public Sex getSex() {
          return sex;
        }

        @Override
        public String getLogin() {
          return login;
        }

        @Override
        public String getLocation() {
          return location;
        }

        @Override
        public Integer getID() {
          return id;
        }

        @Override
        public Update getCurrentStatus() {
          return null;
        }

        @Override
        public String getBackgroundPath() {
          return bg_path;
        }

        @Override
        public Media getBackground() {
          return null;
        }

        @Override
        public String getAvatarPath() {
          return null;
        }

        @Override
        public UserPicture getAvatar() {
          return null;
        }
      };
    } else {
      return null;
    }
  }

  public synchronized User getUser(final Integer user_id) {
    try {
      Connection conn = pool.getConnection();
      PreparedStatement stat = conn.prepareStatement(SQL_US_GETBYID);
      stat.setInt(1, user_id);
      final User value = getUser(stat);
      conn.close();
      return value;
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }

  public synchronized User getUser(final String user_name) {
    try {
      Connection conn = pool.getConnection();
      PreparedStatement stat = conn.prepareStatement(SQL_US_GETBYNAME);
      stat.setString(1, user_name);
      final User value = getUser(stat);
      conn.close();
      return value;
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }
}
